﻿#include "dbservice.h"
#include <QSqlQuery>
#include <QSqlError>
#include "common.h"

static const QString SYS_SETTING_TABLE_NAME("sys_setting");
static const QString DB_VERSION_KEY("db_version_key");
DBService gDBService;

DBService::DBService()
{

}

int DBService::openDB()
{
    LOG_INFO("try to open sqlite with db name:%s, user name:%s, passwd:%s", SQL_DB_NAME, SQL_USER_NAME, SQL_USER_PASSWD);
    database = QSqlDatabase::addDatabase("QSQLITE");
    database.setDatabaseName(SQL_DB_NAME);
    database.setUserName(SQL_USER_NAME);
    database.setPassword(SQL_USER_PASSWD);
    bool openOk =  database.open();
    if(openOk)
    {
        LOG_INFO("open db success");
        return checkAndUpdateDB();

    }
    else
    {
        LOG_ERROR("open db failed");
        return f_fail;
    }
}

int DBService::checkAndUpdateDB()
{
    bool sysSettingTableExist = false;
    if(isTableExist(SYS_SETTING_TABLE_NAME, sysSettingTableExist) == f_success)
    {
        if(!sysSettingTableExist)
        {
            LOG_INFO("sys_setting table not existed, will try to create it and set db version to 0.0f");
            if(createSysSettingTable() == f_success && saveDBVersion(0) == f_success)
            {
                LOG_INFO("create sys_setting and save db version to 0 success");
            }
            else
            {
                LOG_ERROR("create sys setting or save db version failed, check and update db failed");
                return f_fail;
            }
        }
        return doUpdateDB();
    }
    else
    {
        LOG_ERROR("check sys setting table's exist failed, check and update db failed");
        return f_fail;
    }
}
int DBService::doUpdateDB()
{
    int dbVersion = 0;
    if(getDBVersion(dbVersion) == f_success)
    {
        if(dbVersion < SQL_DB_VERSION)
        {
            return doUpdateDBFrom2_0To3_0();
        }
        else
        {
            LOG_INFO("db version:%d same to current db version ,no need to update db", dbVersion);
            return f_success;
        }
    }
    else
    {
        LOG_ERROR("try to get db version failed, do update db failed");
        return f_fail;
    }
}

int DBService::getSysSettingValue(const QString &key, QString & value)
{
    QString sql = "select value from " + SYS_SETTING_TABLE_NAME + " where key = '" + key + "';";

    return queryForString(sql, value);
}

int DBService::deleteSysSettingValue(const QString &key)
{
     QString sql = "delete from " + SYS_SETTING_TABLE_NAME + " where key = '" + key + "';";
     return execSql(sql);
}

int DBService::insertSysSettingValue(const QString &key, const QString &value)
{
    QString sql = "insert into " + SYS_SETTING_TABLE_NAME + " values('" + key + "','" + value + "');";
    return execSql(sql);
}

int DBService::loadAllComputers(QList<Computer> &computers)
{
    computers.clear();

    int iRet = f_fail;

    QString sql = "select id, name, source, ip, comment, apps from computer;";
    QSqlQuery sql_query;
    sql_query.prepare(sql);
    if(!sql_query.exec())
    {
        iRet = f_fail;
        LOG_ERROR("try to exec sql:%s failed, error:%s", QStr2CStr(sql), QStr2CStr(sql_query.lastError().text()));
    }
    else
    {

        iRet = f_success;
        while(sql_query.next())
        {
             Computer  computer(sql_query.value(1).toString(),
                                sql_query.value(2).toInt(),
                                sql_query.value(3).toString(),
                                sql_query.value(4).toString(),
                                sql_query.value(5).toString());
            computer.id = sql_query.value(0).toInt();

            computers.push_back(computer);
        }

         LOG_INFO("try to loadAllComputers success with sql:%s success, result count:%d", QStr2CStr(sql),computers.size());

    }

    return iRet;
}

int DBService::saveOneComputer(Computer &compuer)
{

    QSqlQuery sql_query;

    bool isInsert = (compuer.id < 0);
    if(isInsert)
    {
        int i = 0;
        QString sql = "insert into computer(name,source,ip,comment,apps) values (?,?,?,?,?);";
        sql_query.prepare(sql);
        sql_query.bindValue(i++,compuer.name);
        sql_query.bindValue(i++,compuer.fromSource);
        sql_query.bindValue(i++,compuer.ip);
        sql_query.bindValue(i++,compuer.comment);
        sql_query.bindValue(i++,compuer.apps);
    }
    else
    {
        int i = 0;
        QString sql = "update computer set name=?, source=?, ip=?, comment=?, apps=?  where id = ?;";
        sql_query.prepare(sql);
        sql_query.bindValue(i++,compuer.name);
        sql_query.bindValue(i++,compuer.fromSource);
        sql_query.bindValue(i++,compuer.ip);
        sql_query.bindValue(i++,compuer.comment);
        sql_query.bindValue(i++,compuer.apps);
        sql_query.bindValue(i++,(unsigned int)compuer.id);

    }


    if(sql_query.exec())
    {
        LOG_INFO("try to save one computer success");

        if(isInsert)
        {
            compuer.id = sql_query.lastInsertId().toInt();
        }

        return f_success;
    }
    else
    {
        LOG_ERROR("try to save one computer failed,error:%s", QStr2CStr(sql_query.lastError().text()));
        return f_fail;
    }
}

int DBService::deleteOneComputer(Computer &monitor)
{
    QString sql = "delete from computer where id = " + int2QStr(monitor.id);
    return execSql(sql);
}

int DBService::saveOneComputerAlarmLog(ComputerAlarmLog &log)
{
    QSqlQuery sql_query;

    bool isInsert = (log.id < 0);
    if(isInsert)
    {
        int i = 0;
        QString sql = "insert into computer_alarm_log(computer_id,mode,type,comment,time, is_read) values (?,?,?,?,?,?);";
        sql_query.prepare(sql);
        sql_query.bindValue(i++,log.computerId);
        sql_query.bindValue(i++,log.mode);
        sql_query.bindValue(i++,log.type);
        sql_query.bindValue(i++,log.comment);
        sql_query.bindValue(i++,log.time);
        sql_query.bindValue(i++,log.isRead);
    }
    else
    {
        int i = 0;
        QString sql = "update computer_alarm_log set computer_id=?, mode=?, type=?, comment=?, time=?, is_read=? where id = ?;";
        sql_query.prepare(sql);
        sql_query.bindValue(i++,log.computerId);
        sql_query.bindValue(i++,log.mode);
        sql_query.bindValue(i++,log.type);
        sql_query.bindValue(i++,log.comment);
        sql_query.bindValue(i++,log.time);
        sql_query.bindValue(i++,log.isRead);
        sql_query.bindValue(i++,(unsigned int)log.id);

    }


    if(sql_query.exec())
    {
        LOG_INFO("try to save one computer alarm log success");

        if(isInsert)
        {
            log.id = sql_query.lastInsertId().toInt();
        }

        return f_success;
    }
    else
    {
        LOG_ERROR("try to save one  computer alarm log failed,error:%s", QStr2CStr(sql_query.lastError().text()));
        return f_fail;
    }
}

int DBService::loadComputerAlarmLogs(list<ComputerAlarmLog *> &logs, long *computerId, QString *isRead, QString *alarmType, QString *alarmMode, int maxCount)
{
    SafeClearListAndDelEle(ComputerAlarmLog, logs);

    QSqlQuery sql_query;
    QString sql = "select id, computer_id, mode, type, comment, time, is_read from computer_alarm_log where 1=1";
    if(computerId) sql += " and computer_id = " + QString::number(*computerId) ;
    if(isRead) sql += " and is_read = " + *isRead;
    if(alarmType) sql += " and type = " + *alarmType;
    if(alarmMode) sql += " and mode = '" + *alarmMode + "'";
    sql += " order by time desc ";
    if(maxCount > 0) sql += " limit 0, " + int2QStr(maxCount);

    int iRet = f_fail;

    sql_query.prepare(sql);
    if(!sql_query.exec())
    {
        iRet = f_fail;
        LOG_ERROR("try to exec sql:%s failed, error:%s", QStr2CStr(sql), QStr2CStr(sql_query.lastError().text()));
    }
    else
    {
        while(sql_query.next())
        {
            ComputerAlarmLog * log = new ComputerAlarmLog();
            log->id = sql_query.value(0).toInt();
            log->computerId = sql_query.value(1).toInt();
            log->mode = sql_query.value(2).toString();
            log->type = sql_query.value(3).toInt();
            log->comment = sql_query.value(4).toString();
            log->time = sql_query.value(5).toString();
            log->isRead = sql_query.value(6).toBool();
            logs.push_back(log);
        }

        iRet = f_success;
        LOG_INFO("load computer alarm logs from db success with sql:%s, logs count:%d",QStr2CStr(sql), logs.size());
    }
    return iRet;
}

int DBService::loadComputerAlarmLogsCount(int &count, long *computerId, QString *isRead, QString *alarmType, QString *alarmMode)
{
    QString sql = "select count(1) from computer_alarm_log where 1=1";
    if(computerId) sql += " and computer_id = " + QString::number(*computerId);
    if(isRead) sql += " and is_read = " + *isRead;
    if(alarmType) sql += " and type = " + *alarmType;
    if(alarmMode) sql += " and mode = '" + *alarmMode + "'";

    return queryForInt(sql, count);
}

int DBService::deleteComputerAlarmLogs(long *computerId, QString *isRead, QString *alarmType, QString *alarmMode)
{
    QString sql = "delete from computer_alarm_log where 1=1";
    if(computerId) sql += " and computer_id = " + QString::number(*computerId);
    if(isRead) sql += " and is_read = " + *isRead;
    if(alarmType) sql += " and type = " + *alarmType;
    if(alarmMode) sql += " and mode = '" + *alarmMode + "'";

    return execSql(sql);
}

int DBService::markUnreadComputerAlarmLogsToRead(long *computerId, QString *isRead, QString *alarmType, QString *alarmMode)
{
    QString sql = "UPDATE computer_alarm_log SET is_read = 1 where 1=1";
    if(computerId) sql += " and computer_id = " + QString::number(*computerId);
    if(alarmType) sql += " and type = " + *alarmType;
    if(alarmMode) sql += " and mode = '" + *alarmMode + "';";

    return execSql(sql);
}

int DBService::doUpdateDBFrom2_0To3_0()
{
    int dbVersion = 0;
    if(getDBVersion(dbVersion) == f_success)
    {
        if(dbVersion < 2)
        {
            doUpdateDBFrom1_0To2_0();
        }

        QString sql = "ALTER TABLE computer ADD apps VARCHAR(200) not NULL DEFAULT \"\";";

        if(execSql(sql) == f_success)
        {
            LOG_INFO("try to add column apps to computer success");

        }
        else
        {
            LOG_ERROR("try to add column apps to computer failed");
            return f_fail;
        }
        return saveDBVersion(3);
    }
    else
    {
        LOG_ERROR("try to get db version failed, do doUpdateDBFrom2_0To3_0 failed");
        return f_fail;
    }
}

int DBService::doUpdateDBFrom1_0To2_0()
{
    int dbVersion = 0;
    if(getDBVersion(dbVersion) == f_success)
    {
        if(dbVersion < 1)
        {
            doUpdateDBFrom0_0To1_0();
        }

        {
            QString sql = "CREATE TABLE  IF NOT EXISTS  computer_alarm_log (\
                          'id' INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,\
                          'computer_id' INTEGER NOT NULL,\
                          'mode' VARCHAR(50) NOT NULL,\
                          'type' INTEGER NOT NULL,\
                          'comment' VARCHAR(200), \
                          'time' VARCHAR(50), \
                          'is_read' INTEGER(1) NOT NULL DEFAULT 0\
                    );";

            if(execSql(sql) == f_success)
            {
                LOG_INFO("try to create talbe app_monitor_log success");

            }
            else
            {
                LOG_ERROR("try to create table app_monitor_log failed");
                return f_fail;
            }
        }
        return saveDBVersion(2);
    }
    else
    {
        LOG_ERROR("try to get db version failed, do doUpdateDBFrom1_0To2_0 failed");
        return f_fail;
    }
}

int DBService::doUpdateDBFrom0_0To1_0()
{
    int dbVersion = 0;
    if(getDBVersion(dbVersion) == f_success)
    {
        if(dbVersion == 0)
        {
            QString sql = "CREATE TABLE  IF NOT EXISTS  computer (\
                             'id' INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,\
                             'name' VARCHAR(150)  NOT NULL,\
                             'source' INTEGER  NOT NULL,\
                             'ip' VARCHAR(60)  NOT NULL,\
                             'comment' VARCHAR(200)  NOT NULL\
                            );";
            if(f_success == execSql(sql))
            {
                LOG_INFO("try to create table computer success");
            }
            else
            {
                LOG_ERROR("try to create table computer failed");
                return f_fail;
            }


            return saveDBVersion(1);
        }
        else
        {
            LOG_ERROR("only support update db from 0 to 1.0 in this function");
            return f_fail;
        }
    }
    else
    {
        LOG_ERROR("try to get db version failed, do doUpdateDBFrom0_0To1_0 failed");
        return f_fail;
    }
}

int DBService::createSysSettingTable()
{
    QString sql = "CREATE TABLE IF NOT EXISTS " + SYS_SETTING_TABLE_NAME + "(key varchar(100) not null, value varchar(200));";

    return execSql(sql);
}

int DBService::getDBVersion(int & version)
{
    QString versionStr;
    if(getSysSettingValue(DB_VERSION_KEY, versionStr) == f_success)
    {
        version = versionStr.toInt();
        return f_success;
    }
    else
    {
        return f_fail;
    }
}

int DBService::saveDBVersion(int version)
{

    if(deleteSysSettingValue(DB_VERSION_KEY) == f_success && insertSysSettingValue(DB_VERSION_KEY, int2QStr(version)) == f_success)
    {
        return f_success;
    }
    else
    {
        return f_fail;
    }
}

int DBService::isTableExist(const QString& tableName, bool &exist)
{

    QString sql = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name= '" + tableName + "'";
    int value = 0;
    if(queryForInt(sql, value) == f_success)
    {
        exist = value > 0;
        return f_success;
    }
    else
    {
        LOG_ERROR("try to check if talbe:%s exist with sql:%s failed", tableName, QStr2CStr(sql));
        return f_fail;
    }


}

int DBService::queryForInt(const QString &sql, int &value)
{
    int iRet = f_fail;

    QSqlQuery sql_query;
    sql_query.prepare(sql);
    if(!sql_query.exec())
    {
        iRet = f_fail;
        LOG_ERROR("try to exec sql:%s failed, error:%s", QStr2CStr(sql), QStr2CStr(sql_query.lastError().text()));
    }
    else
    {
        while(sql_query.next())
        {
            iRet = f_success;
            value = sql_query.value(0).toInt();
            break;
        }

        if(iRet == f_success)
        {
            LOG_INFO("get int value:%d, from sql:%s", value, QStr2CStr(sql));
        }
        else
        {
            LOG_ERROR("faild to get int valie from sql:%s, sql result is empty!", QStr2CStr(sql));
        }
    }

    return iRet;

}

int DBService::queryForString(const QString  & sql, QString &value)
{
    int iRet = f_fail;

    QSqlQuery sql_query;
    sql_query.prepare(sql);
    if(!sql_query.exec())
    {
        iRet = f_fail;
        LOG_ERROR("try to exec sql:%s failed, error:%s", QStr2CStr(sql), QStr2CStr(sql_query.lastError().text()));
    }
    else
    {
        while(sql_query.next())
        {
            iRet = f_success;
            value = sql_query.value(0).toString();
            break;
        }

        if(iRet == f_success)
        {
            LOG_INFO("get string value:%s, from sql:%s", QStr2CStr(value), QStr2CStr(sql));
        }
        else
        {
            LOG_WARN("faild to get string valie from sql:%s, sql result is empty!", QStr2CStr(sql));
        }
    }

    return iRet;
}

int DBService::execSql(const QString & sql)
{
    int iRet = f_fail;
    QSqlQuery sql_query;
    if(!sql_query.exec(sql))
    {
        iRet = f_fail;
        LOG_ERROR("try to exec sql:%s failed, error:%s", QStr2CStr(sql), QStr2CStr(sql_query.lastError().text()));
    }
    else
    {
        iRet = f_success;
        LOG_INFO("exec sql:%s success", QStr2CStr(sql));
    }

    return iRet;

}
